package org.noip.milhous;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.noip.milhous.annotations.dbVar;
import org.noip.milhous.annotations.primarykey;
import org.noip.milhous.clause.On;
import org.noip.milhous.clause.OrderBy;
import org.noip.milhous.clause.Utilities;
import org.noip.milhous.clause.Where;
import org.noip.milhous.clause.Where.OPERATOR;
import org.noip.milhous.datastore.Datastore;

public class Database implements Datastore
{

	public static Integer ID = 0;

	public static synchronized Integer getInstanceID()
		{
			Integer result = null;
			//			System.out.println("Before ID" + ID);
			ID = ID + 2;
			//			System.out.println("After ID" + ID);
			result = ID;
			return result;
		}

	private Integer instance = getInstanceID();

	private static final String JAVA_LANG_OBJECT = "java.lang.Object";

	public static final String DELIMITTER = "_";

	private static final String JOIN = " JOIN ";

	// Map<String, DatabaseTable<?>> classList;

	SQL databaseConnection = null;

	private String stringRep;

	/**
	 * Get the connection to the database JDBC
	 * 
	 * @return the current connection
	 */
	public SQL getDatabaseConnection()
		{
			return databaseConnection;
		}

	public Boolean failed()
		{
			return databaseConnection.isFailed();
		}

	/**
	 * Create a new database using the following parameters
	 * 
	 * @param host
	 *          host of the database
	 * @param port
	 *          the port to connect to on the server
	 * @param database
	 *          the database name
	 * @param userid
	 *          user name
	 * @param password
	 *          password for the user
	 */
	public Database(String host, String port, String database, String userid, String password)
		{
			this(new SQL(host, port, database, userid, password));
			stringRep = host + ":" + port + " schema=" + database + " user=" + userid + " pass=" + password;
		}

	/**
	 * Create a new database using the following parameters
	 * 
	 * @param host
	 *          host of the database
	 * @param port
	 *          the port to connect to on the server
	 * @param database
	 *          the database name
	 * @param userid
	 *          user name
	 * @param password
	 *          password for the user
	 */
	public Database(String host, Integer port, String database, String userid, String password)
		{
			this(new SQL(host, port.toString(), database, userid, password));
			stringRep = host + ":" + port + " schema=" + database + " user=" + userid + " pass=" + password;
		}

	@Override
	public String toString()
		{
			return stringRep;
		}

	/**
	 * Create a new database object
	 * 
	 * @param connection
	 *          the connection to a table and schema
	 */
	public Database(SQL connection)
		{
			// classList = new HashMap<String, DatabaseTable<?>>();
			databaseConnection = connection;
		}

	public Database(Connection connection)
		{
			this(new SQL(connection));
		}

	/**
	 * Insert the object into the database
	 * 
	 * @param o
	 *          the item to insert
	 */
	@SuppressWarnings("unchecked")
	public <Type> void insert(Object o)
		{
			DatabaseTable<Type> t = (DatabaseTable<Type>) getTable(o);
			try
				{
					Object primaryKey = t.insertItem(o);
					if (o instanceof PrimaryKey)
						{
							((PrimaryKey) o).setPrimaryKey(primaryKey);
						}
				}
			catch (SQLException e)
				{
					e.printStackTrace();
				}
		}

	@SuppressWarnings("unchecked")
	public <Type> Integer update(Object o, Where w, Object... values)
		{
			DatabaseTable<Type> t = (DatabaseTable<Type>) getTable(o);
			Integer result = null;
			try
				{
					result = t.updateItem(o, w, values);
				}
			catch (SQLException e)
				{
					e.printStackTrace();
				}
			return result;
		}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <Type> List<Type> selectSpecial(Class<?> resultClass, Class<?> tableClass, Where w, Object... params)
		{
			try
				{
					DatabaseTable<Type> resultTable = ((DatabaseTable<Type>) getTable(resultClass));
					DatabaseTable selectTable = getTable(tableClass);
					Type resultInstance = (Type) resultClass.newInstance();
					if (resultInstance instanceof SQLInterface)
						{
							String sql = ((SQLInterface) resultInstance).toSQL();
							return resultTable.selectSpecial(sql, selectTable.getTableName(), w, params);
						}
				}
			catch (Exception e)
				{
					e.printStackTrace();
				}
			return null;
		}

	@SuppressWarnings("unchecked")
	public <Type> List<Type> selectSQL(Class<?> resultClass, String query, Object... params)
		{
			try
				{
					DatabaseTable<Type> resultTable = ((DatabaseTable<Type>) getTable(resultClass));
					//					Type resultInstance = (Type) resultClass.newInstance();
					//					if (resultInstance instanceof SQLInterface)
					//						{
					//							String sql = ((SQLInterface) resultInstance).toSQL();
					return resultTable.selectSQL(query, params);
					//						}
				}
			catch (Exception e)
				{
					e.printStackTrace();
				}
			return null;
		}

	@SuppressWarnings("unchecked")
	public <Type> Integer update(Object o)
		{
			DatabaseTable<Type> t = (DatabaseTable<Type>) getTable(o);
			Integer result = null;
			try
				{
					result = t.updateItem(o, null, null);

				}
			catch (SQLException e)
				{
					e.printStackTrace();
				}
			return result;
		}

	@SuppressWarnings("unchecked")
	public <Type> void setForiegnKey(Object o, Object key, Class<?> Parent)
		{
			DatastoreObject<Type> t = (DatastoreObject<Type>) getTable(o);
			FieldData fkName = t.getForignKey(Parent.getSimpleName());
			if (fkName != null)
				{
					t.setData(o, fkName.javaname, key);
				}
		}

	/**
	 * Get the table structure for the given class. If the class has been seen, then use the cached version, otherwise create a new version and cache it.
	 * 
	 * @param classObject
	 *          the object to create a table class for
	 * @return the Table object for the given class
	 */
	public <Type> DatabaseTable<Type> getTable(Type classObject)
		{
			return getTable(classObject, null);
		}

	/**
	 * Get the table structure for the given class. If the class has been seen, then use the cached version, otherwise create a new version and cache it.
	 * 
	 * @param classObject
	 *          the object to create a table class for
	 * @param tableName
	 *          the table name to use
	 * @return the Table object for the given class
	 */
	@SuppressWarnings({ "rawtypes" })
	public <Type> DatabaseTable<Type> getTable(Type classObject, String tableName)
		{
			DatabaseTable<Type> t = null;
			String name;
			if (!classObject.getClass().getName().equals("java.lang.Class"))
				{
					name = classObject.getClass().getName();
				}
			else
				{
					name = ((Class) classObject).getName();
				}

			// if (classList.get(name) == null)
			// {
			if (DatabaseTable.debug)
				{
					System.out.println(String.format("******************%s#######%s##############", name, tableName));
				}
			try
				{
					t = new DatabaseTable<Type>(Class.forName(name), this, classObject);
				}
			catch (Exception e)
				{
					e.printStackTrace();
				}
			// classList.put(name, t);
			// }
			// else
			// {
			// t = (DatabaseTable<Type>) classList.get(name);
			// }
			if (tableName != null)
				t.setTableName(tableName);
			return t;
		}

	/**
	 * Get the table structure for the given class. If the class has been seen, then use the cached version, otherwise create a new version and cache it.
	 * 
	 * @param classObject
	 *          the object to create a table class for
	 * @return the Table object for the given class
	 */
	// @SuppressWarnings("unchecked")
	public <Type> DatabaseTable<Type> getTable(Class<Type> classObject)
		{
			DatabaseTable<Type> t = null;
				// String name = classObject.getName();
				// if (classList.get(name) == null)
				{
					try
						{
							t = new DatabaseTable<Type>(classObject, this, classObject.newInstance());
							// classList.put(name, t);
						}
					catch (Exception e)
						{
							e.printStackTrace();
						}
				}
			// else
			// {
			// t = (DatabaseTable<Type>) classList.get(name);
			// }
			return t;
		}

	/**
	 * Delete a single item(should be keyed)
	 * 
	 * @param item
	 */
	public void delete(Object item)
		{
			DatabaseTable<?> t = getTable(item);
			t.delete(item);
		}

	/**
	 * Delete an object from the database
	 * 
	 * @param table
	 *          object to delete
	 * @param where
	 *          the where clause to use
	 */
	public void delete(Object table, Where where)
		{
			delete(table, where, null);
		}

	public void deleteWhere(Object item, Where where, Object... args)
		{
			List<Object> arguments = Arrays.asList(args);
			delete(item, where, arguments);
		}

	public void deleteC(Class<?> iteClass, Where where, Object... args)
		{
			List<Object> arguments = Arrays.asList(args);
			DatabaseTable<?> t = getTable(iteClass);
			t.deleteWhereOnly(where, arguments);
		}

	/**
	 * Delete an object from the database
	 * 
	 * @param item
	 *          object to delete
	 * @param where
	 *          the where clause to use p p #>E J
	 * @param argList
	 *          the arguments for the delete
	 */
	public void delete(Object item, Where where, List<Object> argList)
		{
			DatabaseTable<?> t = getTable(item);
			t.delete(item, where, argList);
		}

	/**
	 * Find all the objects in the database
	 * 
	 * @param <Type>
	 * @param type
	 *          the class to find
	 * @param where
	 *          the clause to match with
	 * @return a list of all the objects that match the query from the database
	 */
	public <Type> List<Type> query(Type type, Where where)
		{
			List<Type> result = new LinkedList<Type>();
			DatabaseTable<Type> t = getTable(type);
			result = t.select(where);
			return result;
		}

	/**
	 * Find all the objects in the database
	 * 
	 * @param <Type>
	 * @param type
	 *          the class to find
	 * @return a list of all the objects from the database for the table
	 */
	public <Type> List<Type> query(Type type)
		{
			return this.query(type, null);
		}

	/**
	 * Joins two tables where the primary key of a is the foreign key of b
	 * 
	 * @param <AType>
	 * @param <BType>
	 * @param a
	 *          class with the main key
	 * @param b
	 *          class with foreign key
	 * @return
	 */
	public <AType, BType> Map<AType, BType> joinFK(Class<AType> a, Class<BType> b)
		{
			return joinFK(a, b, null);
		}

	public <AType, BType> Map<AType, BType> joinFK(Class<AType> a, Class<BType> b, Where where, Object... params)
		{
			DatabaseTable<AType> aTable = getTable(a);
			DatabaseTable<BType> bTable = getTable(b);
			FieldData data = bTable.getForignKey(aTable.getTableName());
			On onClause = new On(a.getSimpleName(), aTable.getTableKey(), bTable.getTableName(), data.databasename);
			return join(a, b, onClause, where, params);
		}

	/**
	 * Do a join on Two tables
	 * 
	 * @param <AType>
	 *          the type for table A
	 * @param <BType>
	 *          the type of Table B
	 * @param a
	 *          the first table
	 * @param b
	 *          the second table
	 * @param onClause
	 *          the one to use for the join
	 * @return
	 */
	public <AType, BType> Map<AType, BType> join(DatabaseTable<AType> a, DatabaseTable<BType> b, On onClause)
		{
			return join(a, b, onClause, null);
		}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <AType, BType> Map<AType, BType> join(Class a, Class b, On onClause)
		{
			DatabaseTable<AType> aTable = getTable(a);
			DatabaseTable<BType> bTable = getTable(b);
			return join(aTable, bTable, onClause, null);
		}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public <AType, BType> Map<AType, BType> join(Class a, Class b, On onClause, Where where, Object... fields)
		{
			DatabaseTable<AType> aTable = getTable(a);
			DatabaseTable<BType> bTable = getTable(b);
			return join(aTable, bTable, onClause, where, fields);
		}

	@SuppressWarnings("unchecked")
	public <Type> List<Type> select(Class<?> className, Where where, Object... fields)
		{
			DatabaseTable<Type> temp = (DatabaseTable<Type>) getTable(className);
			return temp.select(where, fields);
		}

	@SuppressWarnings("unchecked")
	public <Type> List<Type> selectOrderBy(Class<?> className, OrderBy orderby)
		{
			DatabaseTable<Type> temp = (DatabaseTable<Type>) getTable(className);
			return temp.selectInternal(orderby);
		}

	public <Type> List<Type> selectAll(Class<?> className)
		{
			return select(className, null);
		}

	public <AType, BType> Map<AType, BType> join(DatabaseTable<AType> a, DatabaseTable<BType> b, On onClause, Where where, Object... f)
		{
			LinkedList<Object> temp = new LinkedList<Object>(Arrays.asList(f));
			return joinInternal(a, b, onClause, where, temp);
		}

	/**
	 * Do a join on Two tables
	 * 
	 * @param <AType>
	 *          the type for table A
	 * @param <BType>
	 *          the type of Table B
	 * @param a
	 *          the first table
	 * @param b
	 *          the second table
	 * @param onClause
	 *          the one to use for the join
	 * @param where
	 *          the where clause to limit the results
	 * @return a map between the two tables as joined
	 */
	public <AType, BType> Map<AType, BType> joinInternal(DatabaseTable<AType> a, DatabaseTable<BType> b, On onClause, Where where, LinkedList<Object> params)
		{
			HashMap<AType, BType> results = new HashMap<AType, BType>();
			String sqlString = "SELECT * FROM " + Utilities.quoteString(a.getTableName()) + JOIN + Utilities.quoteString(b.getTableName()) + " " + onClause.toSQL();
			if (where != null)
				{
					sqlString += " " + where.toSQL();
				}
			if (DatabaseTable.debug)
				{
					System.out.println(sqlString);
				}
			try
				{
					ResultSet data = databaseConnection.queryDataBase(sqlString, params);
					while (data.next())
						{
							AType aValue = a.createInstance(data, a.getFields(), a.getTableName());
							BType bValue = b.createInstance(data, b.getFields(), b.getTableName());
							results.put(aValue, bValue);
						}
					data.close();
				}
			catch (SQLException e)
				{
					e.printStackTrace();
				}
			return results;
		}

	public String fieldsToString(LinkedList<FieldData> fields, String tableName)
		{
			StringBuffer result = new StringBuffer();
			String del = "";
			for (FieldData f : fields)
				{
					result.append(del + tableName + "." + f.databasename + " AS " + tableName + DELIMITTER + f.databasename);
					del = " , ";
				}
			return result.toString();
		}

	/**
	 * Select a single element from the table
	 * 
	 * @param <Type>
	 *          the return type
	 * @param className
	 *          the class to get
	 * @param where
	 *          the where clause
	 * @param params
	 *          the list of parameters
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <Type> Type selectSingle(Class<?> className, Where where, Object... params)
		{
			DatabaseTable<Type> table = (DatabaseTable<Type>) getTable(className);
			return table.selectSingle(where, params);
		}

	/**
	 * Select a single entry from a table given the Key
	 * 
	 * @param className
	 * @param key
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <Type> Type selectSingleKey(Class<?> className, Object key)
		{
			DatabaseTable<Type> table = (DatabaseTable<Type>) getTable(className);
			return table.selectSingle(new Where(Utilities.quoteString(table.getTableKey()), OPERATOR.EQUAL, "?"), key.toString());
		}

	@SuppressWarnings("unchecked")
	public <Type> Type selectSingleKeyI(Class<?> className, Integer key)
		{
			DatabaseTable<Type> table = (DatabaseTable<Type>) getTable(className);
			return table.selectSingle(new Where(Utilities.quoteString(table.getTableKey()), OPERATOR.EQUAL, "?"), key);
		}

	/**
	 * Get a list by the foreign keys from another table
	 * 
	 * 
	 * @param className
	 * @param primaryKeyObject
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <Type> List<Type> selectByForiegnKey(Class<?> className, Object primaryKeyObject, OrderBy... orderedBy)
		{
			DatabaseTable<Type> table = (DatabaseTable<Type>) getTable(className);
			DatabaseTable<Object> parentTable = (DatabaseTable<Object>) getTable(primaryKeyObject.getClass());
			String Base = parentTable.getTableName();
			// String Base = ((TableNameMapping) primaryKeyObject).getRealTableName();

			FieldData data = table.getForiegnKey(Base);
			if (data == null)
				{
					System.err.println("FK does not exist" + className.getCanonicalName() + " to " + primaryKeyObject.getClass().getCanonicalName());
				}
			return table.select(new Where(data.databasename, OPERATOR.EQUAL, "?", orderedBy), getPrimaryKey(primaryKeyObject));
		}

	public static Object getPrimaryKey(Object o)
		{
			Object result = null;
			if (o instanceof DatabaseRow)
				{
					result = ((DatabaseRow) o).getPrimaryKey();
				}
			else
				{
					Set<Field> methods = getListOfFields(o.getClass());
					for (Field m : methods)
						{
							if (m.getAnnotation(primarykey.class) != null)
								{
									try
										{
											m.setAccessible(true);
											result = m.get(o);
										}
									catch (Exception e)
										{
											e.printStackTrace();
										}
								}
						}
				}
			return result;
		}

	/**
	 * Select a single item by the foreign key relationship
	 * 
	 * @param <Type>
	 * @param className
	 * @param parentObject
	 * @return
	 */
	public <Type> Type selectByForiegnKeySingle(Class<?> className, Object parentObject)
		{
			List<Type> temp = selectByForiegnKey(className, parentObject);
			Type result = null;
			if (temp.size() > 0)
				{
					result = temp.remove(0);
				}
			return result;
		}

	/**
	 * Select the foreign Key using the where clause that references the primary key
	 * 
	 * @param <Type>
	 * @param resultClassName
	 * @param primaryKeyObject
	 * @param where
	 * @return
	 */
	public <Type> List<Type> selectByForeignKey(Class<?> resultClassName, DatabaseRow primaryKeyObject, Where where)
		{
			List<Type> temp = null;
			temp = select(resultClassName, where, primaryKeyObject.getPrimaryKey().toString());
			return temp;
		}

	public <ChildType, ParentType> ChildType selectByParentKeySingle(Class<?> className, Object parentObject)
		{
			DatabaseTable<Object> table = getTable(parentObject);
			String Base = className.getSimpleName();
			FieldData data = table.getForiegnKey(Base);
			Object value = table.getData(parentObject, data.databasename);
			ChildType result = selectSingleKey(className, value.toString());
			return result;
		}

	// @SuppressWarnings("unchecked")
	// public <Type> Type deepClone(Object object, Class<?> newClass, ClassPair... classes)
	// {
	// DatabaseTable<Type> table = (DatabaseTable<Type>) getTable(newClass);
	// return table.deepClone(object, classes);
	// }

	@SuppressWarnings("unchecked")
	public <Type, IType> List<Type> select(Class<?> resultType, DatabaseRow key, Class<?> intermediate)
		{
			DatabaseTable<?> table = getTable(intermediate);
			String Base = ((TableNameMapping) key).getRealTableName();
			FieldData data = table.getForiegnKey(Base);
			Map<Type, IType> bob =
					(Map<Type, IType>) joinFK(
							resultType,
							intermediate,
							new Where(key.getPrimaryKey(), OPERATOR.EQUAL, Utilities.quoteString(table.getTableName()) + "." + Utilities.quoteString(data.databasename)));
			return new LinkedList<Type>(bob.keySet());
		}

	public Boolean isClosed()
		{
			Boolean closed = true;
			try
				{
					closed = getDatabaseConnection().getConnectionObject().isClosed();
				}
			catch (SQLException e)
				{
					e.printStackTrace();
				}

			return closed;
		}

	public void closeConnection()
		{
			try
				{
					getDatabaseConnection().getConnectionObject().close();
				}
			catch (SQLException e)
				{
					e.printStackTrace();
				}

		}

	public static void setDebug(Boolean debug)
		{
			DatabaseTable.setDebug(debug);
		}

	public int getRowsLastInsert()
		{
			return this.databaseConnection.getRowsLastInsert();
		}

	@SuppressWarnings("unchecked")
	public static <RTYPE, ITYPE> List<RTYPE> convert(List<ITYPE> inputList, Class<?> resultClass)
		{
			List<RTYPE> result = new LinkedList<RTYPE>();
			try
				{
					for (ITYPE i : inputList)
						{
							Object temp = resultClass.newInstance();
							copyData(i, temp);
							result.add((RTYPE) temp);
						}
				}
			catch (Exception e)
				{

				}

			return result;
		}

	public static <S, T> T copyData(S from, T to)
		{
			Set<Field> fromFields = getListOfFields(from.getClass());
			Set<Field> toFields = getListOfFields(to.getClass());
			for (Field toField : toFields)
				{
					Field FromFieldItem = get(toField, fromFields);
					if (FromFieldItem != null)
						{
							try
								{
									Object data = getData(from, FromFieldItem.getName());
									setData(to, toField, data);
								}
							catch (Exception e)
								{
									e.printStackTrace();
								}

						}
				}
			return to;
		}

	private static Field get(Field f, Set<Field> fields)
		{
			Field result = null;
			for (Field a : fields)
				{
					if (f.getName().equals(a.getName()))
						{
							result = a;
							break;
						}
				}
			return result;
		}

	private static void setData(Object o, Field privateStringField, Object value)
		{
			try
				{
					privateStringField.setAccessible(true);
					privateStringField.set(o, value);
				}
			catch (Exception e)
				{
					e.printStackTrace();
				}
		}

	private static Set<Field> getListOfFields(@SuppressWarnings("rawtypes") Class myClass)
		{
			Set<Field> result = new HashSet<Field>();
			Field[] fields = myClass.getDeclaredFields();
			for (Field f : fields)
				{
					if (f.getAnnotation(dbVar.class) != null)
						{
							result.add(f);
						}
				}
			if (!myClass.getCanonicalName().equals(JAVA_LANG_OBJECT))
				{
					result.addAll(getListOfFields(myClass.getSuperclass()));
				}
			return result;
		}

	private static Object getData(Object o, String field)
		{
			Field privateStringField;
			Object result = null;
			try
				{
					privateStringField = getField(o.getClass(), field);
					result = privateStringField.get(o);
				}
			catch (Exception e)
				{
					for (Field f : o.getClass().getFields())
						{
							System.err.println("--" + f.getName());
						}
					e.printStackTrace();
				}
			return result;
		}

	@SuppressWarnings("rawtypes")
	private static Field getField(Class currentClass, String field)
		{
			Field result = null;
			try
				{
					result = currentClass.getDeclaredField(field);
				}
			catch (SecurityException e)
				{
					e.printStackTrace();
				}
			catch (NoSuchFieldException e)
				{
					// result = getField(c.getSuperclass(), field);
					result = getField(currentClass.getSuperclass(), field);
				}
			if (result.isAccessible() == false)
				{
					result.setAccessible(true);
				}
			return result;
		}

	public Integer getConnection()
		{
			Integer result = -1;
			if (databaseConnection != null)
				{
					result = databaseConnection.getConnectionID();
				}
			return result;
		}

	public Integer getCurrentID()
		{
			return instance;
		}
}
